<?php  if ( ! defined('BASEPATH')) exit('No direct script access allowed');

/**
 * @filename magent.php
 * 网吧地区代理模型
 * @author hmw
 *
 */

class Magent extends Model {

    function Magent() {
        parent::Model();
    }

    function insert_new_agent($data){
        return $this->db->insert('agents',$data);
    }

    function get_agent_count($area_id) {
        $this->db->where(array('area_id'=>$area_id));
        return $this->db->count_all_results('agents');
    }

    function get_agents($area_id,$offset,$limit) {
        $this->db->where(array('area_id'=>$area_id));
        $this->db->limit($limit,$offset);
        $query = $this->db->get('agents');
        return $query->result_array();
    }

    function get_agent_info($agent_id) {
        $this->db->where(array('agent_id'=>$agent_id));
        $query = $this->db->get('agents');
        return $query->row_array();
    }

    function get_agent_info_by_no($agent_no) {
        $this->db->where(array('agent_no'=>$agent_no));
        $query = $this->db->get('agents');
        return $query->row_array();
    }

    function update_agent($agent_id,$data) {
        return $this->db->update('agents',$data,array('agent_id'=>$agent_id));
    }

    function delete_agent($agent_id) {
        return $this->db->delete('agents',array('agent_id'=>$agent_id));
    }

    function check_agent_user($no,$pwd) {
        $query = $this->db->get_where('agents',array('agent_no'=>$no));
        $row = $query->row_array();
        if(empty($row)){
            return '-2';
        } else {
            if($row['agent_pwd'] == $pwd){
                return $row['agent_id'];
            } else {
                return '-3';
            }
        }
    }

    function get_agent_order_count($agent_no){
        $sql = "SELECT id 
                FROM {$this->db->dbprefix}orders
                WHERE uid IN (SELECT bar_uid FROM {$this->db->dbprefix}netbars WHERE agent='$agent_no')";
        $query = $this->db->query($sql);
        return $query->num_rows();
    }

    function get_agent_orders($agent_no,$offset=0,$limit=10){
        $sql = "SELECT * 
                FROM {$this->db->dbprefix}orders
                WHERE uid IN (SELECT bar_uid FROM {$this->db->dbprefix}netbars WHERE agent='$agent_no')
                GROUP BY uid
                LIMIT $offset,$limit";
        $query = $this->db->query($sql);
        return $query->result_array();
    }

    function get_agent_orders_month_count($agent_no,$year,$month){
        $sql = "SELECT id 
                FROM {$this->db->dbprefix}orders
                WHERE uid IN (SELECT bar_uid FROM {$this->db->dbprefix}netbars WHERE agent='$agent_no')
                    AND year(order_time)=$year
                    AND month(order_time)=$month";
        $query = $this->db->query($sql);
        return $query->num_rows();
    }

    function get_agent_month_price($agent_no,$year,$month){
        $sql = "SELECT SUM(price) AS price
                FROM {$this->db->dbprefix}orders
                WHERE uid IN (SELECT bar_uid FROM {$this->db->dbprefix}netbars WHERE agent='$agent_no')
                    AND year(order_time)=$year
                    AND month(order_time)=$month";
        $query = $this->db->query($sql);
        $row = $query->row_array();
        return empty($row) ? 0 :$row['price'];
    }

    function get_agent_orders_month($agent_no,$year,$month,$offset=0,$limit=10){
        $sql = "SELECT * 
                FROM {$this->db->dbprefix}orders
                WHERE uid IN (SELECT bar_uid FROM {$this->db->dbprefix}netbars WHERE agent='$agent_no')
                    AND year(order_time)=$year
                    AND month(order_time)=$month
                GROUP BY uid
                LIMIT $offset,$limit";
        $query = $this->db->query($sql);
        return $query->result_array();
    }

    function get_nb_orders_month_count($agent_no,$year,$month){
        $sql = "SELECT uid
                FROM {$this->db->dbprefix}orders
                WHERE uid IN (SELECT bar_uid FROM {$this->db->dbprefix}netbars WHERE agent='$agent_no')
                    AND year(order_time)=$year
                    AND month(order_time)=$month
                GROUP BY uid";
        $query = $this->db->query($sql);
        return $query->num_rows();
    }

    function get_nb_orders_month($agent_no,$year,$month,$offset=0,$limit=10){
        $sql = "SELECT uid,sum(price) as order_price,count(*) as order_count
                FROM {$this->db->dbprefix}orders
                WHERE uid IN (SELECT bar_uid FROM {$this->db->dbprefix}netbars WHERE agent='$agent_no')
                    AND year(order_time)=$year
                    AND month(order_time)=$month
                GROUP BY uid
                LIMIT $offset,$limit";
        $query = $this->db->query($sql);
        return $query->result_array();
    }

    function get_agent_history_order_count($agent_no,$year,$month){
        $sql = "SELECT count(*) AS total 
                FROM {$this->db->dbprefix}orders
                WHERE uid IN (SELECT bar_uid FROM {$this->db->dbprefix}netbars WHERE agent='$agent_no')
                    AND year(order_time)!=$year 
                    AND month(order_time)!=$month";
        $query = $this->db->query($sql);
        $row = $query->row();
        return $row->total;
    }

    function get_agent_history_orders($agent_no,$year,$month,$offset=0,$limit=10){
        $sql = "SELECT *
                FROM {$this->db->dbprefix}orders
                WHERE uid IN (SELECT bar_uid FROM {$this->db->dbprefix}netbars WHERE agent='$agent_no')
                    AND year(order_time)!=$year
                    AND month(order_time)!=$month
                LIMIT $offset,$limit";
        $query = $this->db->query($sql);
        return $query->result_array();
    }

    function get_agent_history_dicount($agent_no,$year,$month){
        $sql = "SELECT SUM(price) AS price 
                FROM {$this->db->dbprefix}orders
                WHERE uid IN (SELECT bar_uid FROM {$this->db->dbprefix}netbars WHERE agent='$agent_no')
                    AND year(order_time)!=$year
                    AND month(order_time)!=$month";
        $query = $this->db->query($sql);
        $row = $query->row();
        $price = $row->price;
        $percent = get_config_value('agent_discount');
        $discount = round($price*$percent/100,2);
        return $discount;
    }

    function get_agent_nb_count($agent_no){
        $sql = "SELECT bar_uid FROM {$this->db->dbprefix}netbars WHERE agent='$agent_no'";
        $query = $this->db->query($sql);
        return $query->num_rows();
    }

}

?>